﻿/// -----------------------------------------------------------------
/// BookmarkManipulator.cs: provides methods for extracting, 
/// updating and deleting Bookmarks in the Database.
/// License: see http://mve.codeplex.com/license; All Rights follows the MS-PL
/// Current owner: shiniwa
/// The project decription: please refer to http://codeplex.com/mve/
/// -----------------------------------------------------------------
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Xml;

namespace MVEContentSvc.BookmarkOperations
{
	/*
	 * 日本SGI株式会社
	 * ヨハンソン・ヘンリク
	 * johansson@sgi.co.jp
	 * 
	 * Change Log:
	 * ---------------------------------------------------------------------------
	 * 2009.01.07	Johansson Henrik		Class Created.
	 * 2009.01.09							Updated QueryBookmarksForUser to always return an XML string.
	 *										Currently the application expect the reply to be an empty
	 *										Xml Document even if an exception is thrown or no matching 
	 *										data was founds.
	 */

	/// <summary>
	/// This static class provides methods for extracting, 
	/// updating and deleting Bookmarks in the Database.
	/// Use the static methods in this class to perform
	/// Bookmark related operations on the Database.
	/// </summary>
	/// <remarks>INSERT:Creds</remarks>
    internal static class BookmarkManipulator
    {
        /// <summary>
        /// Get all the Bookmarked segments from the DB for the given User DB id.
        /// Original Name = QueryBookmarks
        /// </summary>
        /// <param name="userDbId">Users DB id</param>
        /// <param name="connection">Sql Connection</param>
        /// <returns>The Xml string that contains the response document.</returns>
        internal static string QueryBookmarksForUser(int userDbId, SqlConnection connection)
        {
            SqlCommand command = new SqlCommand();
            SqlDataReader reader = null;
            command.CommandTimeout = 60;

            List<ClipOperations.Segment> _bookmarkedSegments =
                new List<ClipOperations.Segment>();
            List<Bookmark> _usersBookmarks =
                new List<Bookmark>();

            //Build The final result document
            XmlDocument doc = null;
            XmlElement dataRoot = XmlConstructor.FormatResponseDocument(ref doc);
            XmlElement clipsElementRoot = doc.CreateElement("clips");
            dataRoot.AppendChild(clipsElementRoot);

            string result = string.Empty;
            try
            {
                command.Connection = connection;
                command.CommandType = System.Data.CommandType.StoredProcedure;
                command.CommandText = "GetBookmarksByUserId";

                command.Parameters.Add("userId", SqlDbType.Int).Value = userDbId;

                reader = command.ExecuteReader();

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        _usersBookmarks.Add(new Bookmark()
                        {
                            Id = reader.GetInt32(3),
                            SegmentId = reader.GetInt32(0),
                            UserId = reader.GetInt32(1)
                        });
                    }
                }

                command.Dispose();
                reader.Close();
                reader.Dispose();

                //Get each matching segment from the database.
                foreach (Bookmark bm in _usersBookmarks)
                {
                    ClipOperations.Segment segment =
                        ClipOperations.SegmentLoader.LoadSegmentFromDatabase(
                        bm.SegmentId, connection);

                    if (segment != null)
                        _bookmarkedSegments.Add(segment);
                }

                foreach (ClipOperations.Segment segment in _bookmarkedSegments)
                {
                    ClipOperations.Clip clip =
                        ClipOperations.ClipLoader.LoadClipFromDatabase(
                        segment.ParentClipId, connection);

                    if (clip != null)
                    {
                        XmlElement clipRoot = XmlConstructor.AddClipNodeToDocument(
                            doc, clip, clipsElementRoot);
                        XmlElement segmentRoot = XmlConstructor.AddSegmentNodeToClip(
                            doc, segment, clip.OnAirDate, clipRoot);

                        XmlElement kwRoot = doc.CreateElement("keywords");
                        segmentRoot.AppendChild(kwRoot);

                        List<KeywordOperations.Keyword> segmentKeywords =
                            KeywordOperations.KeywordLoader.LoadKeywordsForSegmentFromDatabase(
                            segment.Id, connection);

                        if (segmentKeywords != null)
                        {
                            foreach (KeywordOperations.Keyword keyword in segmentKeywords)
                            {
                                XmlConstructor.AddKeywordNodeToSegment(
                                doc, keyword, kwRoot);
                            }
                        }
                    }
                }

                result = doc.OuterXml;

            }
            catch (NullReferenceException ex)
            {
                ErrorHandler.LogMessage(ex.Message, false);
                result = doc.OuterXml;
            }
            catch (SqlException ex)
            {
                ErrorHandler.LogMessage(ex.Message, false);
                result = doc.OuterXml;
            }
            catch (InvalidOperationException ex)
            {
                ErrorHandler.LogMessage(ex.Message, false);
                result = doc.OuterXml;
            }
            catch (IndexOutOfRangeException ex)
            {
                ErrorHandler.LogMessage(ex.Message, false);
                result = doc.OuterXml;
            }

            finally
            {
                command.Dispose();
                if (reader != null)
                {
                    reader.Close();
                    reader.Dispose();
                }
            }

            return result;
        }//end:QueryBookmarksForUser

        /// <summary>
        /// Bookmark a segment in the Database.
        /// </summary>
        /// <remarks>Done</remarks>
        /// <param name="userDbId">The users DB id</param>
        /// <param name="segmentId">Id of the segment to bookmark</param>
        /// <param name="connection">SQL Connection</param>
        /// <returns>True if the Bookmark was added</returns>
        internal static bool AddSegmentBookmark(int userDbId, int segmentId, SqlConnection connection)
        {
            SqlCommand command = new SqlCommand();
            command.CommandTimeout = 60;

            bool result = false;
            try
            {
                command.Connection = connection;
                command.CommandType = System.Data.CommandType.StoredProcedure;
                command.CommandText = "AddBookmarkForSegment";

                command.Parameters.Add("userId", SqlDbType.Int).Value = userDbId;
                command.Parameters.Add("segmentId", SqlDbType.Int).Value = segmentId;

                SqlParameter returnValue = new SqlParameter("@RETURN_VALUE", SqlDbType.Int);
                returnValue.Direction = ParameterDirection.ReturnValue;
                command.Parameters.Add(returnValue);

                command.ExecuteNonQuery();
                int resultCode = (int)returnValue.Value;

                if (resultCode == 0)
                {
                    result = true;
                }
            }
            catch (InvalidOperationException ex)
            {
                ErrorHandler.LogMessage(ex.Message, false);
                result = false;
            }
            catch (InvalidCastException ex)
            {
                ErrorHandler.LogMessage(ex.Message, false);
                result = false;
            }
            finally
            {
                command.Dispose();
            }

            return result;
        }//end:AddSegmentBookmark

        /// <summary>
        /// Delete a Bookmark segment in the Database.
        /// </summary>
        /// <remarks>Done</remarks>
        /// <param name="userDbId">The users DB id</param>
        /// <param name="segmentId">Id of the segment whose bookmark will be deleted</param>
        /// <param name="connection">SQL Connection</param>
        /// <returns>True if the Bookmark was deleted</returns>
        internal static bool DeleteSegmentBookmark(int userDbId, int segmentId, SqlConnection connection)
        {
            SqlCommand command = new SqlCommand();
            command.CommandTimeout = 60;

            bool result = false;
            try
            {
                command.Connection = connection;
                command.CommandType = System.Data.CommandType.StoredProcedure;
                command.CommandText = "DeleteBookMarkForSegment";

                command.Parameters.Add("userId", SqlDbType.Int).Value = userDbId;
                command.Parameters.Add("segmentId", SqlDbType.Int).Value = segmentId;

                SqlParameter returnValue = new SqlParameter("@RETURN_VALUE", SqlDbType.Int);
                returnValue.Direction = ParameterDirection.ReturnValue;
                command.Parameters.Add(returnValue);

                command.ExecuteNonQuery();
                int resultCode = (int)returnValue.Value;

                if (resultCode == 0)
                {
                    result = true;
                }
            }
            catch (InvalidOperationException ex)
            {
                ErrorHandler.LogMessage(ex.Message, false);
                result = false;
            }
            catch (InvalidCastException ex)
            {
                ErrorHandler.LogMessage(ex.Message, false);
                result = false;
            }
            finally
            {
                command.Dispose();
            }

            return result;
        }//end:DeleteSegmentBookmark
    }
}
